-- Function: tracker_system.data_entry_qc_ets_get_all_detail_error(integer, date, date)

-- DROP FUNCTION tracker_system.data_entry_qc_ets_get_all_detail_error(integer, date, date);

CREATE OR REPLACE FUNCTION tracker_system.data_entry_qc_ets_get_all_detail_error(integer, date, date)
  RETURNS SETOF quality_error_ets AS
$BODY$
DECLARE 
	v_project_id alias FOR $1;	
	v_exp_start_time alias for $2;
	v_exp_end_time alias for $3;
	v_column_name_key record;
	v_str_column_name_key character varying(255) := '';
	v_str_column_name_key_select character varying(255) := '';
	v_column_name_error record;
	v_str_column_name_error character varying(255) := '';
	v_str_column_name_error_select character varying(255) := '';
	v_strQuery character varying (40000) :='';
	--v_project_id int;
BEGIN
--select * from dataentry_ets
--DROP table dataentry_ets
--v_project_id := (select project_id from tracker_system.project where cls_project_id =v_cls_project_id);

--select * from tracker_system.data_entry_qc_ets_get_all_detail_error('352', '2013-06-01', '2013-06-30')
--select * from tracker_system.data_entry_qc_ets_get_all_qc_detail('352', '2013-06-01', '2013-06-30')
-- colum key
	FOR v_column_name_key 
		IN SELECT distinct column_name FROM tracker_system.template_column a, tracker_system.project b 
				      WHERE a.template_id = b.template_id and b.project_id = v_project_id and a.is_key = true
	        LOOP	
		          v_str_column_name_key := v_str_column_name_key||' '||v_column_name_key||' text,';
		          v_str_column_name_key_select := v_str_column_name_key_select||' '||v_column_name_key||',';
	END LOOP;
	--raise notice '%',v_str_column_name_key;
	----------------------------------change
	v_str_column_name_key := replace(replace(replace(v_str_column_name_key,'"',''),'(','("'),')','")');
	v_str_column_name_key_select := replace(replace(replace(v_str_column_name_key_select,'"',''),'(','("'),')','")');
	
	if(v_str_column_name_key <> '' and v_str_column_name_key_select <> '') THEN
	----------------------------------- change
		v_str_column_name_key := substring((replace(replace(v_str_column_name_key,'(',''),')','')),1,length((replace(replace(v_str_column_name_key,'(',''),')','')))-1);
		v_str_column_name_key_select := substring((replace(replace(v_str_column_name_key_select,'(',''),')','')),1,length((replace(replace(v_str_column_name_key_select,'(',''),')','')))-1);
		--raise notice '%',v_str_column_name_key;
	--- column error
		FOR v_column_name_error 
			IN SELECT distinct column_name FROM tracker_system.template_column a, tracker_system.project b 
					      WHERE a.template_id = b.template_id and b.project_id = v_project_id and a.is_key = false
			LOOP	
				  v_str_column_name_error := v_str_column_name_error||' '||v_column_name_error||' text,';
				  v_str_column_name_error_select := v_str_column_name_error_select||' '||v_column_name_error||' ,';
		END LOOP;
		--raise notice '%',v_str_column_name_error;
	---------------------------- change
		v_str_column_name_error := replace(replace(replace(v_str_column_name_error,'"',''),'(','("'),')','")');
		v_str_column_name_error_select := replace(replace(replace(v_str_column_name_error_select,'"',''),'(','("'),')','")');
	----------------------------chang
		v_str_column_name_error := substring((replace(replace(v_str_column_name_error,'(',''),')','')),1,length((replace(replace(v_str_column_name_error,'(',''),')','')))-1);
		v_str_column_name_error_select := substring((replace(replace(v_str_column_name_error_select,'(',''),')','')),1,length((replace(replace(v_str_column_name_error_select,'(',''),')','')))-1);
		--raise notice '%',v_str_column_name_error;
		--DROP table dblink_exec_table; 	
		v_strQuery := 'select a.project_id,full_file_name,b.field_name_system::text as field_name,typist_data,qc_data,export_date,qc_card_id
		from (
		select '||v_project_id||' as project_id,"Path name" as full_file_name, "Field name" as field_name,"Typist data" as typist_data,"QC data" as qc_data,export_date,a.qc_card_id from 
		(SELECT a.array_name[1] as qc_card_id, a.array_name[2] as qc_date, a.array_name[3] as qc_user,a.array_name[4] as export_date, a.array_name[5]::int as export_times,
		'||v_str_column_name_key_select||'  FROM public.crosstab
		
				(''select ARRAY[q.qc_card_id::text, t.qc_date::text, q.qc_user , t.export_date::text,t.export_times::text] As array_name, c.column_name , k.column_value
			from tracker_system.tracker_info t, tracker_system.qc_card q, tracker_system.key_value k,tracker_system.template_column c
			where t.project_id= '||v_project_id||' AND
			      t.approve_status = true AND
			      
			      TO_DATE(t.qc_date,''''dd.MM.yyyy'''') >='''''||v_exp_start_time||''''' AND
			      TO_DATE(t.qc_date,''''dd.MM.yyyy'''') <='''''||v_exp_end_time||''''' AND
			      t.tracker_info_id = q.tracker_info_id AND
			      q.qc_card_id = k.qc_card_id AND
			      c.column_id = k.column_id AND
			      c.is_key=true
			      Order by q.qc_card_id
			      ;'', ''SELECT distinct column_name FROM tracker_system.template_column a, tracker_system.project b 
					      WHERE a.template_id = b.template_id and b.project_id = '||v_project_id||' and a.is_key = true'')
			as a(array_name text[],'||v_str_column_name_key||') ) as b right join 
		(select a.array_name[1] as qc_card_id,a.array_name[2] as field_name,'||v_str_column_name_error_select||' 
			from 
			crosstab
			(''SELECT ARRAY[a.qc_card_id::text, a.field_name::text] As array_name,tc.column_name,a.error_info_value
				from (select qc.qc_card_id,ei.error_info_value,fn.field_name,ei.template_column_id
				      from tracker_system.qc_card qc, tracker_system.error_info ei, tracker_system.error_type et,
				      tracker_system.field_name fn, tracker_system.tracker_info t
				      where t.project_id = '||v_project_id||' and
					    t.approve_status = true AND				 
					    TO_DATE(t.qc_date,''''dd.MM.yyyy'''') >='''''||v_exp_start_time||''''' AND
					    TO_DATE(t.qc_date,''''dd.MM.yyyy'''') <='''''||v_exp_end_time||''''' AND
					    qc.tracker_info_id= t.tracker_info_id AND
					    qc.qc_card_id = ei.qc_card_id and ei.field_id = fn.field_id and ei.error_type_id = et.error_type_id 
					    and et.is_valid = true and et.error_name=''''Typing'''') as a, 
					   tracker_system.template_column tc where tc.column_id=a.template_column_id
					   order by a.qc_card_id,a.field_name
					   '',
				''select distinct a.column_name from tracker_system.template_column a,tracker_system.project b where a.template_id=b.template_id and a.is_key = false and b.project_id= '||v_project_id||' '')
				as a (array_name text[],'||v_str_column_name_error||')) as a on (a.qc_card_id::int = b.qc_card_id::int) 
				order by TO_DATE(b.qc_date,''dd.MM.yyyy''), TO_DATE(b.export_date,''dd.MM.yyyy''),b.export_times) as a, 
				tracker_system.map_field_name b
				where lower(a.field_name) = lower(b.field_name_ets) and a.project_id = b.project_id';	
		raise notice '%',v_strQuery;
		RETURN QUERY 
		execute v_strQuery;	
	END IF;


END;	
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION tracker_system.data_entry_qc_ets_get_all_detail_error(integer, date, date)
  OWNER TO postgres;

